Oracle 10g Database Administration Training in Republic, Missouri

1. Overview of Oracle Database

ORACLE_HOME and ORACLE_SID

The Oracle Database vs. The Oracle Instance

Instance Memory Structures

Background Processes

Server Processes

Datafiles

Blocks, Extents, and Segments

Control Files

Redo Logs

The Oracle Architecture

SYS and SYSTEM Users

2. Starting and Shutting Down an Oracle Database

SYSDBA and SYSOPER System

Privileges

Oracle Database Startup

Oracle Database Shutdown

Altering Database Availability

Suspending and Resuming a Database

Quiescing a Database

Tracking Database Activity - The Alert

Log

Data Dictionary Views for Startup and

Shutdown

3. Using The Oracle Data Dictionary

Introducing the Data Dictionary

DBA, ALL, and USER Data Dictionary

Views

V$ Dynamic Performance Views

Using Oracle Documentation to Locate

Data Dictionary Views

Combining Data Dictionary Views

4. Oracle Database Users and Schemas

Users and Schemas

Oracle Default Database Users

Oracle Sample Schemas

Creating Users

Altering and Dropping Users

Data Dictionary Views For Users

5. Oracle System Privileges

Overview of System Privileges

Granting and Revoking System

Privileges

Data Dictionary Views for System

Privileges

Roles

Creating and Removing Roles

Data Dictionary Views for Roles

Pre-defined Roles

User Group PUBLIC

6. Parameter Files

Oracle Database Parameters

The Parameter File (PFILE) and the

Server Parameter File (SPFILE)

Dynamic vs. Static Parameters

Determining Current Parameter Settings

Benefits of SPFILEs over PFILEs

Creating an SPFILE from a PFILE and

Back Again

Understanding Scope

Session-Level Parameters

Data Dictionary Views for Parameters

7. Datafiles and Tablespaces

Datafiles Overview

Tablespaces Overview

SYSTEM and SYSAUX Tablespaces

Creating Tablespaces

Dictionary- and Locally Managed

Tablespaces

Locally Managed Tablespace Extent

Allocation

Temporary Tablespaces

Temporary Tablespace Groups

Default Tablespaces

Tablespace Quotas

Dropping and Altering a Tablespace

Renaming a Tablespace

Renaming or Relocating Datafiles

Bigfile Tablespaces

Data Dictionary Views for Datafiles and

Tablespaces

8. Control Files

Control Files Overview

Database Parameters for Control Files

Backing Up Control Files

Restoring Control Files from

Multiplexed Copies

Restoring Control Files from Backups

Moving Control Files

Data Dictionary Views for Control Files

9. Oracle Physical Structures — Online

Redo Logs

Redo

Redo Log Files

Database Parameters for Redo

Sizing the Redo Log Files

How Many Redo Log Groups?

Creating Redo Logs

Removing Redo Logs

Renaming Redo Log Files

Forcing Log Switches

Archiving Redo Logs

Configuring Archive Log Mode

Database Parameters for Archiving

Data Dictionary Views for Redo Logs

10. Oracle Physical Structures — Undo Segments

Undo Overview

Database Parameters for Undo

Creating Undo Tablespaces

Altering and Dropping Undo

Tablespaces

Switching Undo Tablespaces

Undo Advisor

Data Dictionary Views for Undo

11. Segment Space Management

Blocks, Extents and Segments

Segment Space Management

Fragmentation

Coalescing Fragmented Space

Row Migration and Chaining

Manual Segment Space Usage

Automatic Segment Space Management

Data Dictionary Views for Physical

Objects

12. Tables

Tables Overview

Physical Properties of Tables

Creating Tables

Table Storage and Logging

Altering Tables

Dropping Tables

The Recycle Bin

The TRUNCATE COMMAND

Temporary Tables

Clustered Tables

Indexed-Organized Tables

Creating an IOT

Data Dictionary Views for Tables

13. Indexes

Indexes

B-tree Indexes

NULL Values and Indexes

NULL Values and Unique Indexes

Creating Indexes

Monitoring Index Usage

Dropping Indexes

Rebuilding and Moving Indexes

Coalescing Indexes

Bitmap Indexes

Special Index Types

Index Key Compression

Data Dictionary Views for Indexes

14. Constraints

Integrity Constraints

Constraint Names and Syntax

Constraint Checking

Managing Primary Key Constraints

Managing NOT NULL Constraints

Managing Check Constraints

Managing Foreign Key Constraints

Data Dictionary Views for Constraints

15. Views

Views

Creating and Replacing Views

Data Dictionary Views for Views

Security Through Views

Altering and Dropping Views

Dependencies and Views

Updating Data Through Views

16. Object Privileges

Object Privileges

Granting and Revoking Object

Privileges

Using WITH GRANT OPTION

Revoking and the GRANT OPTION

Data Dictionary Views for Object

Privileges

17. Synonyms

Synonyms

Private and Public Synonyms

Creating and Dropping Synonyms

Security and Synonyms

Object Precedence

18. The Optimizer and Statistics

Optimizer Overview

Optimizer Statistics

Data Dictionary Views and Statistics

Collecting Statistics

Removing Statistics

Automated Statistics Collection

19. Oracle Net Services

Oracle Networking Explained

The Net Configuration Assistant

Configuring the Listener — GUI

Configuring the Listener — Manually

Configuring the Client — GUI

Configuring the Client — Manually

20. Data Pump

Oracle Data Pump Architecture

Configuring for Data Pump

Command-Line Utilities

Exporting With expdp

Importing With impdp

Interactive Mode

Attaching to a Running Job

Parallel Data Pump

Data Dictionary Views for Data Pump

21. SQL*Loader and External Tables

SQL*Loader

SQL*Loader Control File

Loading Data with SQL*Loader

Conventional vs. Direct Path Loads

External Tables Overview

Creating an External Table

Creating a Writable External Table

22. Back Up Your Database

Physical Backups vs. Logical Backups

Offline Backup Basics

Oracle Offline Backup Steps

Other Backup Considerations

Advanced Backup Concepts

RMAN

23. Recover Your Database

Offline Recovery Basics

Restoration Using Offline Backups for a

NOARCHIVE Database

Restoring Other Files

Other Recovery Considerations

24. Oracle Enterprise Manager

Introducing Oracle Enterprise Manager

OEM Architecture and Physical

Structure

Installing OEM When Using DBCA

Using EMCA to Install OEM

Managing OEM

Removing OEM

Troubleshooting OEM

Securing OEM

Starting OEM

Introducing the OEM Home Pages

Using OEM — Managing the Job

Scheduler

Using OEM — Using Metrics, Alerts, and Thresholds

Automatic Workload Repository

The Automatic Database Diagnostic

Monitor — ADDM

OEM and Advisors

The SQL Access Advisor

25. Appendix A – Installing Oracle Software

Optimal Flexible Architecture

OFA Directory Layouts

The Oracle Universal Installer

OUI Installation Modes

Preparing To Install Software

Starter Database

Installing the Oracle Software

Deinstalling Oracle Software

26. Appendix B – Creating a Database

Using the DBCA

The DBCA

Select a Database Template

Specify the Database Name

Set System Account Passwords

Specify File Locations

Install Sample Schemas

Specify Storage Parameters

Create Your Oracle Database

Oracle Net Configuration Assistant

Configure the Listener

Set TCP Parameters

Removing a Database with the DBCA

27. Appendix C – Manual Database Creation

Manual Database Creation

Prepare the System

Creating the Parameter File

The CREATE DATABASE Statement

Manually Create the Database

Finalizing the Creation

SQL.BSQ File

Creating the Service on Windows

28. Appendix D – Auditing the Database

Auditing Explained

Data Dictionary Views for Auditing

Audit Trail, OS, and DB

Statement Auditing

Privilege Auditing

Object Auditing